package db;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import RofMission.RofMissionParams;
import main.MapFile;
import main.MapProp;
import main.Prop;
import missObjects.AirfieldsModelList;
import missObjects.FilteredAirfields;
import missObjects.RofMissObjAirfield;
import missObjects.RofMissObjBlock;
import missObjects.RofMissObjGroup;
import missObjects.RofTemplateObject;

/**
 * Handle the database init
 * 
 * @author lefuneste
 *
 */
public class RofDB {

	private static Connection con = null;
	private static Logger LOGGER = Logger.getLogger("RofLogger");

	static {
		LOGGER.getUseParentHandlers();
	}

	/**
	 * Open driver access
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void open() throws ClassNotFoundException, SQLException {
		try {
			Class.forName("org.sqlite.JDBC");
			con = DriverManager
					.getConnection("jdbc:sqlite:DB/RofTakeOffLanding.db");

		} finally {
			// if (con != null) con.close();
		}
		LOGGER.getUseParentHandlers();
		LOGGER.info("Opened database successfully");
	}

	public static Connection getCon() {
		return con;
	}

	public static void close() throws SQLException {
		if (con != null)
			con.close();
		LOGGER.info("Closed database successfully");
	}

	/**
	 * init of Database: table creation if not existing
	 * 
	 * @throws SQLException
	 */
	public static void init() throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "CREATE TABLE IF NOT EXISTS AIRFIELDFILTER"
					+ "(ID 			INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
					+ " name     	TEXT    NOT NULL, "
					+ " fileID		INT 	NOT NULL, " + " airfieldID	INT		NOT NULL" +
					// " UNIQUE (name,fileID,airfieldID)" +
					");";
			stmt.executeUpdate(sql);
			
			sql = "CREATE TABLE IF NOT EXISTS AIRFIELDSELECTED"
					// + "(ID 			INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
					+ "( airfieldID	INT		NOT NULL,"
					+ " fileID		INT 	NOT NULL, " + 
					" UNIQUE (fileID,airfieldID)" +
					");";
			stmt.executeUpdate(sql);
			
			sql = "CREATE TABLE IF NOT EXISTS "
					+ Prop.get("AIRFIELD") + " "
					+ "(id INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
					+ " indexInFile        INTEGER     NOT NULL,"
					+ " groupID       		INTEGER     NOT NULL,"
					+ " name          		TEXT    	NOT NULL,"
					+ " fileID          	INTEGER    	NOT NULL,"
					+ " mapID          	TEXT    NOT NULL, "
					+ " Xpos          		REAL    NOT NULL, "
					+ " Ypos          		REAL    NOT NULL, "
					+ " Zpos          		REAL    NOT NULL, "
					+ " Xori          		REAL    NOT NULL, "
					+ " Yori          		REAL    NOT NULL, "
					+ " Zori          		REAL    NOT NULL, "
					+ " model         		TEXT    NOT NULL, "
					+ " XLP	     		REAL    NOT NULL, "
					+ " ZLP	      		REAL    NOT NULL, "
					+ " OriLP      		REAL    NOT NULL, "
					+ " filtered      		BOOLEAN NOT NULL, "
					+ " UNIQUE (indexInFile,fileID)" + ");";
			stmt.executeUpdate(sql);
			LOGGER.fine(Prop.get("AIRFIELD") + " Table initialized");
			
			sql = "CREATE TABLE IF NOT EXISTS AIRFIELDBACKUP ("
					+ " indexInFile        INTEGER     NOT NULL,"
					+ " name          		TEXT    	NOT NULL,"
					+ " fileID          	INTEGER    	NOT NULL,"
					+ " XLP	     		REAL    NOT NULL, "
					+ " ZLP	      		REAL    NOT NULL, "
					+ " OriLP      		REAL    NOT NULL, "
					+ " UNIQUE (indexInFile,fileID)" + ");";
			stmt.executeUpdate(sql);
			LOGGER.fine(Prop.get("AIRFIELD") + " Table initialized");		
			
			sql = "CREATE TABLE IF NOT EXISTS "
					+ "AIRFIELDFIX "
					+ "(id INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
					+ " fileID          	INTEGER    	NOT NULL,"
					+ " indexInFile        INTEGER     NOT NULL,"	
					+ " XLP	     		REAL    NOT NULL, "
					+ " ZLP	      		REAL    NOT NULL, "
					+ " OriLP      		REAL    NOT NULL, "
					+ " UNIQUE (indexInFile,fileID)" + ");";
			stmt.executeUpdate(sql);
			LOGGER.fine(Prop.get("AIRFIELDFIX") + " Table initialized");
			
			sql = "CREATE TABLE IF NOT EXISTS " + Prop.get("BLOCK")
					+ " "
					+ "(id INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
					+ " indexInFile        INTEGER     NOT NULL,"
					+ " groupID       		INTEGER     NOT NULL,"
					+ " name          		TEXT    	NOT NULL,"
					+ " objectType      	TEXT    	NOT NULL,"
					+ " fileID          	INTEGER    	NOT NULL,"
					+ " mapID          	TEXT    NOT NULL, "
					+ " Xpos          		REAL    NOT NULL, "
					+ " Ypos          		REAL    NOT NULL, "
					+ " Zpos          		REAL    NOT NULL, "
					+ " Xori          		REAL    NOT NULL, "
					+ " Yori          		REAL    NOT NULL, "
					+ " Zori          		REAL    NOT NULL, "
					+ " model         		TEXT    NOT NULL, "
					+ " UNIQUE (indexInFile,fileID)" + ");";
			stmt.executeUpdate(sql);
			LOGGER.fine(Prop.get("BLOCK") + " Table initialized");
			
			// Creation of GROUP Table 

			sql = "CREATE TABLE IF NOT EXISTS rofGroup"
						+ "(id INTEGER PRIMARY KEY     AUTOINCREMENT NOT NULL,"
						+ " indexInFile        	INTEGER     NOT NULL,"
						+ " name          		TEXT    	NOT NULL,"
						+ " fileID          	INTEGER    	NOT NULL,"
						+ " mapID          	TEXT    NOT NULL, "
						+ " UNIQUE (indexInFile,fileID)" + ");";
				stmt.executeUpdate(sql);
				LOGGER.fine("rofGroup Table initialized");


			// Creation of RawData table 
			sql = "CREATE TABLE IF NOT EXISTS " + Prop.get("RAWDATA") + " "
					+ "(id 					INTEGER 	NOT NULL,"
					+ " objID          		INTEGER    	NOT NULL,"
					+ " fileID          	INTEGER    	NOT NULL,"
					+ " RawData          	TEXT    	NOT NULL,"
					+ " UNIQUE (objID, fileID, RawData)" + " );";
			stmt.executeUpdate(sql);
			LOGGER.fine(Prop.get("RAWDATA") + " Table initialized");
			
		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
		LOGGER.info("AIRFIELDFILTER table created if not exists");
	}

	/**
	 * To start speeding up write requests
	 * @throws SQLException
	 */
	public static void beginTransaction() throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "BEGIN TRANSACTION;";
			stmt.executeUpdate(sql);
		}
		LOGGER.info("Begin Transaction");
	}

	/**
	 *  To end speeding up write requests
	 * @throws SQLException
	 */
	public static void endTransaction() throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "END TRANSACTION;";
			stmt.executeUpdate(sql);
		}
		LOGGER.info("End Transaction");
	}

	/**
	 * suppress all block corresponding to a file in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeBlockInDB(int fileid)
			throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM RofBlock"
					+ " WHERE fileID = " + fileid + " ;";
			System.out.println(sql);
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("rofBlock Table purged into DB for fileID "+fileid);
		}
	}

	/**
	 * suppress all block corresponding in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeBlockInDB() throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM RofBlock;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("rofBlock Table completely purged into DB");
		}
	}

	/**
	 * suppress  group in the DB related to a fileID
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for group to delete
	 * @throws Exception
	 */
	static public void purgeGroupInDB(int fileid)
			throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM RofGroup"
					+ " where fileID = " + fileid + " ;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Group Table purged into DB for fileID "+fileid);

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * suppress all group in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for group to delete
	 * @throws Exception
	 */
	static public void purgeGroupInDB()
			throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM RofGroup"
					+ " ;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Group Table purged into DB for fileID ");

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * suppress all rawdata corresponding to a file in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeRawDataInDB(int fileid)
			throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM RawData"
					+ " WHERE fileID = " + fileid + " ;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("rawdata Table purged into DB for fileID "+fileid);
		}
	}
	
	/**
	 * suppress all rawdata  in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeRawDataInDB()
			throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM RawData"
					+ ";";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("rawdata Table purged into DB");
		}
	}
	
	/**
	 * suppress all backup data corresponding to a file in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeBackupAirfieldInDB(int fileid)
			throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM AirfieldBackup"
					+ " WHERE fileID = " + fileid + " ;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("AirfieldBackup Table purged into DB for fileID "+fileid);
		}
	}

	
	/**
	 * suppress all backup data in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileid
	 *            file filter for block to delete
	 * @throws SQLException
	 */
	static public void purgeBackupAirfieldInDB()
			throws SQLException {
		try (Statement stmt = con.createStatement()) {
			String sql = "DELETE FROM AirfieldBackup"
					+ ";";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("AirfieldBackup Table purged into DB ");
		}
	}


	/**
	 * suppress AirfieldFilter in the DB for a specific fileID
	 * 
	 * @param con
	 *            : connector to DB
	 * @param fileID
	 *            file filter for data to delete
	 * @throws Exception
	 */
	static public void purgeAirfieldFilterInDB(int fileid) throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM AIRFIELDFILTER where fileID = "
					+ fileid + " ;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Airfield filter Table purged into DB for fileID "
					+ fileid);

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}

	/**
	 * suppress all AirfieldFilter in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @throws Exception
	 */
	static public void purgeAirfieldFilterInDB() throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM AIRFIELDFILTER;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Airfield filter Table completely purged into DB for fileID");
		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * suppress all Airfields in the DB
	 * 
	 * @param con
	 *            : connector to DB
	 * @throws Exception
	 */
	static public void purgeAirfieldInDB() throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM AIRFIELD;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Airfield Table completely purged into DB for fileID");
		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	
	/**
	 * Return a list of block object references found by the SQL request 
	 * @param request : request to execute on DB, that must return indexInFile, fileID . Example of request : select indexInFile, fileID from AIRFIELDSELECTED'
	 * @return list of blocks objects
	 * @throws Exception 
	 */
	static public List <RofObjRef> findRofObj(String request) throws Exception {
		
		List <RofObjRef> returnedList=new ArrayList<RofObjRef>();
		
		ResultSet results1 = null;
		try (Statement stmt = con.createStatement()) {
			results1 = stmt.executeQuery(request);
			
			while (results1.next()) {
				RofObjRef blockRef = new RofObjRef();
				blockRef.setObjID(results1.getInt("indexInFile"));
				blockRef.setFileID(results1.getInt("fileID"));
				returnedList.add(blockRef);
			}

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
		
		return returnedList;
		
	}
	
	/**
	 * suppress all element in table AIRFIELDSELECTED
	 * @throws Exception 
	 */
	public static void cleanAirfieldSelected() throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "delete FROM AIRFIELDSELECTED;";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.info("Airfield select Table completely purged into DB");

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * Add an Airfield to the list of selected airfield
	 * @return 
	 * @throws Exception
	 */
	public static void addAirfieldSelected(int fileID, int airfieldID) throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "INSERT OR IGNORE INTO AIRFIELDSELECTED" +
					" (airfieldID, fileID) VALUES('"+
					airfieldID+"',"+fileID+"');"+
					 "";
			stmt.executeQuery(sql);
			stmt.close();
			LOGGER.fine("Airfield "+airfieldID+","+fileID+"added into AIRFIELDSELECTED");

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * Add a set of Airfield to the list of selected airfield with a sql request
	 * @request: a request that must return indexInFile, fileID . 
	 * @throws Exception
	 */
	public static void addAirfieldSelected(String request) throws Exception {
		try {
			Statement stmt = con.createStatement();
			String sql = "INSERT OR IGNORE INTO AIRFIELDSELECTED " +
					request+
					 "";
			stmt.executeUpdate(sql);
			stmt.close();
			LOGGER.fine("Airfield added into AIRFIELDSELECTED using request");

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
	}
	
	/**
	 * Return the informations for selected airfields
	 * @return list of selected airfield informations
	 * @throws Exception
	 */
	public static List <RofLandingPointRef> getAirfieldsSelected() throws Exception {
		
		// String request="select A.name, A.fileID, A.indexInFile, XLP, ZLP, ORILP from AIRFIELDSELECTED AS S JOIN AIRFIELD AS A on ( A.indexInFile=S.airfieldID and A.fileID=S.fileID)  where filtered = 'false'";
		
		String request="select A.name, A.fileID, A.indexInFile, A.XLP, A.ZLP, A.ORILP, B.XLP as XLPbak, B.ZLP as ZLPbak, B.ORILP as ORILPbak from AIRFIELDSELECTED AS S "+
				"JOIN AIRFIELD AS A on ( A.indexInFile=S.airfieldID and A.fileID=S.fileID) "+
				"JOIN AIRFIELDBACKUP AS B on ( A.indexInFile=B.indexInFile and A.fileID=B.fileID) "+
				" where filtered = 'false'";
		
		List <RofLandingPointRef> returnedList=new ArrayList<RofLandingPointRef>();
		
		ResultSet results1 = null;
		try (Statement stmt = con.createStatement()) {
			results1 = stmt.executeQuery(request);
			
			while (results1.next()) {
				RofLandingPointRef LP = new RofLandingPointRef(results1);
				returnedList.add(LP);
			}

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
		return returnedList;
	}
	
	/**
	 * Update the Landing point position in DB
	 * @param lp
	 * @throws SQLException 
	 */
	public static void updateLP(RofLandingPointRef lp, double XLP, double ZLP, double OriLP) throws SQLException {
		
		String request="update airfield set XLP = "+XLP+", ZLP="+ZLP+", oriLP="+OriLP+
				" WHERE fileID="+lp.getFileID()+" AND indexInFile="+lp.getObjID()+
				";";

		Statement stmt = con.createStatement();
		stmt.executeUpdate(request);
		stmt.close();
	}
	
	/**
	 * Update airfields names
	 * @param lp
	 * @throws SQLException 
	 */
	public static void updateAirfieldNames() throws SQLException {	
		Statement stmt = con.createStatement();

		String sql = "drop table if exists t1;"+
		"create table if not exists t1 (indexinfile int, groupID int, name TEXT);"+
		"insert into t1 select A.indexinfile, A.groupID, '\"'||replace(G.name,'\"','')||'-'||replace(A.model,'\"','')||'\"' from AIRFIELD AS A JOIN rofGROUP AS G on ( A.groupID=G.IndexInFile and A.fileID=G.fileID) where groupid<>-1;"+
		"update airfield set name=(select name from t1 where (airfield.indexinfile=t1.indexinfile and airfield.groupID=t1.groupID)) where airfield.groupID <>-1;";
		
		stmt.executeUpdate(sql);
		stmt.close();
		LOGGER.fine("Airfield names updated");
	}

	/**
	 * Update airfields LP with previously modified ones
	 * @param lp
	 * @throws SQLException 
	 */
	public static void updateAirfieldsLP() throws SQLException {	
		Statement stmt = con.createStatement();
		String sql = "update airfield set "+
				"XLP =(select XLP from AirfieldFIX where (airfield.indexinfile=AirfieldFIX.indexInfile and airfield.fileID=AirfieldFIX.fileID) ),"+
				"ZLP =(select  ZLP from AirfieldFIX where (airfield.indexinfile=AirfieldFIX.indexInfile and airfield.fileID=AirfieldFIX.fileID) ),"+
				"OriLP =(select  OriLP from AirfieldFIX where (airfield.indexinfile=AirfieldFIX.indexInfile and airfield.fileID=AirfieldFIX.fileID) )"+
				"where exists (select * from airfieldfix where (airfield.indexinfile=AirfieldFIX.indexInfile and airfield.fileID=AirfieldFIX.fileID))";	
		stmt.executeUpdate(sql);
		stmt.close();
		LOGGER.fine("Airfield modified Landing points updated");
	}
	
	/**
	 * Update airfields names for a specific fileID
	 * @param lp
	 * @throws SQLException 
	 */
	public static void  createBackupinfos(int fileID, String mapName) throws SQLException {
	
		Statement stmt = con.createStatement();
		String sql = "insert into airfieldbackup select indexInFile, name, fileID, XLP, ZLP, OriLP FROM AIRFIELD where fileID="+fileID+
				 "";
		stmt.executeUpdate(sql);
		stmt.close();
		LOGGER.fine("Backup of airfield of map "+mapName+" added into DB");
	}
	
	/**
	 * Update airfields names
	 * @param lp
	 * @throws SQLException 
	 */
	public static void  createBackupinfos() throws SQLException {
	
		Statement stmt = con.createStatement();
		String sql = "insert into airfieldbackup select indexInFile, name, fileID, XLP, ZLP, OriLP FROM AIRFIELD";
		stmt.executeUpdate(sql);
		stmt.close();
		LOGGER.fine("Backup of all airfields added into DB");
	}
	
	/**
	 * Re read all datas for the current map files (contained in params)
	 * @param params
	 * @throws Exception 
	 */
	public static void templateRead(RofMissionParams params) throws Exception {
			
		// purge of objects related to map files
		purgeBlockInDB();
		purgeAirfieldInDB();
		purgeGroupInDB();
		purgeRawDataInDB();
		purgeBackupAirfieldInDB();
		
		String[] mapsNames= MapProp.getMapkeys();
		
		// use of Transaction to improve SQLite performance
		RofDB.beginTransaction();
		
		//processing of all maps
		for( String mapName : mapsNames  ) {
			List <MapFile> test = MapProp.getMapFiles(mapName);
			for (MapFile m : test) {
				// Read Template file
				String templateName=params.getRofPath()+"\\"+m.getFileName();
				Path path2file = Paths.get(templateName);
				int fileID=m.getFileNumber();
				String mapID=params.getCurrentMap();
				int groupID = -1;
				String type = "";

				try (BufferedReader reader = Files.newBufferedReader(path2file,StandardCharsets.ISO_8859_1)) {
					do {
						RofTemplateObject objectReader = new RofTemplateObject();
						
						groupID = objectReader.readFromFile(reader, mapID, fileID, groupID);
						type = objectReader.getObjectType();
						if (type != null) {
							switch (type) {
							case "Group":
								RofMissObjGroup objGroup = new RofMissObjGroup(objectReader);
								objGroup.storeToDB(RofDB.getCon());
								break;
							case "Block": case "Bridge":
								RofMissObjBlock objBlock = new RofMissObjBlock(objectReader);
								objBlock.storeToDB(RofDB.getCon());
								break;
							case "Airfield":
								RofMissObjAirfield objAirfield = new RofMissObjAirfield(objectReader, RofDB.getCon());
								objAirfield.storeToDB(RofDB.getCon());
								break;
							}
						}
					} while (type != null);
	
				}
			}	
			
			// use of Transaction to improve SQLite performance
			RofDB.endTransaction();
			//Modify  name of airfield in group
			updateAirfieldNames();		
			//init Backup airfield data 
			createBackupinfos();
			//update modified LP in airfields
			updateAirfieldsLP();
			
		}				
	}
	
	/**
	 * Re read all Aifield models datas 
	 * @param params
	 * @throws Exception 
	 */
	public static void AirfieldModlesReRead(RofMissionParams params) throws Exception {
		
		RofDB.purgeAirfieldFilterInDB();
		FilteredAirfields.init(RofDB.getCon());
		String objPath=params.getGTPPath()+"\\scripts\\luascripts\\worldobjects";
		 try { AirfieldsModelList.ReadFromFile(objPath);
		 	AirfieldsModelList.storeToDB(RofDB.getCon());
		 } catch (IOException e) 
		 { 
			 // TODO Auto-generated catch block
			 e.printStackTrace(); 
		 }

		AirfieldsModelList.readFromDB(RofDB.getCon());
	}

	/**
	 * Create an export file from airfields info in DB
	 * @param filename
	 * @throws Exception 
	 */
	public static void CreateExportFile(String filename, boolean allAifieldFlag, RofMissionParams params ) throws Exception {
		String request="";
		//file creation
		Path exportFilePath = Paths.get(filename);
		if (!Files.exists(exportFilePath)) Files.createFile(exportFilePath);
		BufferedWriter writer = Files.newBufferedWriter(exportFilePath, Charset.defaultCharset());
		writer.write("File;ID;Name;XLP;YLP;ZLP;ORILP;XRLP("+params.getTakeoffDist()+"m);YLRP;ZRLP"+params.getTakeoffDist()+"m);RORILP;XLPbak;ZLPbak;OriLPbak\n");
		
		if (allAifieldFlag) {
		
			request="select A.name, A.fileID, A.mapID, A.indexInFile, A.XLP, A.YPOS, A.ZLP, A.ORILP, B.XLP as XLPbak, B.ZLP as ZLPbak, B.ORILP as ORILPbak from AIRFIELDSELECTED AS S "+
					"JOIN AIRFIELD AS A on ( A.indexInFile=S.airfieldID and A.fileID=S.fileID) "+
					"JOIN AIRFIELDBACKUP AS B on ( A.indexInFile=B.indexInFile and A.fileID=B.fileID) "+
					" where filtered = 'false'";
			
		} else
		{
			request="select A.name, A.fileID, A.mapID, A.indexInFile, A.XLP, A.YPOS, A.ZLP, A.ORILP, B.XLP as XLPbak, B.ZLP as ZLPbak, B.ORILP as ORILPbak from AIRFIELD AS A "+
					"JOIN AIRFIELDBACKUP AS B on ( A.indexInFile=B.indexInFile and A.fileID=B.fileID) "+
					" where filtered = 'false'";
		}
		
		
		ResultSet results1 = null;
		try (Statement stmt = con.createStatement()) {
			results1 = stmt.executeQuery(request);		
			while (results1.next()) {
				int reverse=-1;
				String name = results1.getString("name");
				String mapID = results1.getString("mapID");
				int fileID=results1.getInt("fileID");
				int indexInFile=results1.getInt("indexInFile");
				String fileName=MapProp.getMapFileName(mapID,fileID);
				Double XLP=Math.round(results1.getDouble("XLP")*1000)/1000.0;
				Double YLP=Math.round(results1.getDouble("YPOS")*1000)/1000.0;
				Double ZLP=Math.round(results1.getDouble("ZLP")*1000)/1000.0;
				Double ORILP=results1.getDouble("ORILP");
				Double XLPbak=Math.round(results1.getDouble("XLPbak")*1000)/1000.0;
				Double OriLPbak=results1.getDouble("ORILPbak");
				Double ZLPbak=Math.round(results1.getDouble("ZLPbak")*1000)/1000.0;							
				Double XRLP=Math.round((XLP-reverse*(params.getTakeoffDist()*Math.cos(Math.toRadians(ORILP))))*1000)/1000.0;
				Double YRLP=YLP;
				Double ZRLP=Math.round((ZLP-reverse*(params.getTakeoffDist()*Math.sin(Math.toRadians(ORILP))))*1000)/1000.0;
				Double RORILP=(ORILP+180)%360;
				writer.write(fileName+";"+indexInFile+";"+name+";"+XLP+";"+YLP+";"+ZLP+";"+ORILP+";"+XRLP+";"+YRLP+";"+ZRLP+";"+ORILP+";"+XLPbak+";"+ZLPbak+";"+OriLPbak+"\n");
				
			}
			writer.close();

		} catch (Exception e) {
			LOGGER.severe(e.getClass().getName() + ": " + e.getMessage());
			throw (e);
		}
		
	}
	
}
